import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

public class DBTools {  
  
    private java.sql.Connection connection;  
    private java.sql.PreparedStatement preparedStatement;  
  
    public DBTools() {  
        try {  
            connection = DriverManager.getConnection(  
                    "jdbc:mysql://192.168.0.248/test?useUnicode=true&characterEncoding=UTF-8", "root", "a+rquQe5KMGKKkmFJkl0");  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
  
    static {  
        try {  
            Class.forName("com.mysql.jdbc.Driver");  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
  
    public void close() {  
        if (connection != null) {  
            try {  
                connection.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  
  
    public ResultSet executeQuery(String sql, LinkedList<Object> params) {  
        try {  
            preparedStatement = connection.prepareStatement(sql);  
            if (params != null) {  
                int i = 1;  
                for (Object p : params) {  
                    preparedStatement.setObject(i++, p);  
                }  
            }  
            return preparedStatement.executeQuery();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return null;  
    }  
    
    public static void testJdb() throws Exception{
    	String[] cityMap = {"济南"};
    	
    	DBTools tool = new DBTools();  
    	//shopMap["中山"] = ["昌兴商行,中山市东升同乐市场正对门/0760-22225956","石岐锦建商行,中山市石岐金马广场1栋/13928122226"];
    	for(int i = 0;i < cityMap.length; i++){
    		StringBuilder result = new StringBuilder();
    		LinkedList<Object> params = new LinkedList<Object>();
    		String city = cityMap[i];
    		result.append("shopMap[\"" + city + "\"] = [");
            params.add(city + "市");  
            ResultSet rs = tool.executeQuery("SELECT `门店名称`,`详细地址`,`联系人`,`联系电话` from `山东点数明细2` where locate(?,详细地址)", params);  
            while(rs.next()){
            	String name = rs.getString(1);
            	String address = rs.getString(2);
            	String link = rs.getString(3);
            	String phone = rs.getString(4);
            	result.append("\"" + name + "," + address + "/" + link + phone +"\",");
            }
            String temp = result.toString().substring(0, result.toString().length() - 1);
            result = new StringBuilder(temp);
            result.append("];");
            System.out.println(result.toString());
    	}
    	
        tool.close();  
    }
    
    public static void testWxtickets() throws Exception{
    	String[] cityMap = {"青岛","潍坊","济南","烟台","淄博","临沂","济宁","威海","泰安","东营","菏泽","德州","聊城","滨州","日照","莱芜","枣庄"};
    	
    	DBTools tool = new DBTools();  
    	//shopMap["中山"] = ["昌兴商行,中山市东升同乐市场正对门/0760-22225956","石岐锦建商行,中山市石岐金马广场1栋/13928122226"];
    	for(int i = 0;i < cityMap.length; i++){
    		StringBuilder result = new StringBuilder();
    		LinkedList<Object> params = new LinkedList<Object>();
    		String city = cityMap[i];
    		result.append("shopMap[\"" + city + "\"] = [");
            params.add(city);  
            ResultSet rs = tool.executeQuery("SELECT 影院名称,地址 from 山东微信电影票 WHERE 城市=?", params);  
            while(rs.next()){
            	String name = rs.getString(1);
            	String address = rs.getString(2);
            	result.append("\"" + name + "," + address + "\",");
            }
            String temp = result.toString().substring(0, result.toString().length() - 1);
            result = new StringBuilder(temp);
            result.append("];");
            System.out.println(result.toString());
    	}
        tool.close();  
    }
    
    public static void test() throws Exception{
    	DBTools tool = new DBTools();  
    	LinkedList<Object> params = new LinkedList<Object>();
        params.add(1);  
        ResultSet rs = tool.executeQuery("SELECT 商品编号,批次号 from 720话费 WHERE 1=?", params);  
        while(rs.next()){
        	StringBuilder result = new StringBuilder();
        	String id = rs.getString(1);
        	String batchNo = rs.getString(2);
        	result.append("INSERT INTO `swift_const_item` VALUES (null, 'cmbc_invoicing_params', '"+batchNo+"', '"+id+"', '0', '1','');");
        	System.out.println(result.toString());
        }
    }
  
    public static List<String> testGetSKu() throws Exception{
    	List<String> list = new ArrayList<String>();
    	DBTools tool = new DBTools();  
    	LinkedList<Object> params = new LinkedList<Object>();
        params.add(1);  
        ResultSet rs = tool.executeQuery("SELECT 商品条形码 from 完税 WHERE 1=?", params);  
        while(rs.next()){
        	StringBuilder result = new StringBuilder();
        	String id = rs.getString(1);
        	list.add(id);
        }
        return list;
    }
    
//    public static void testNewNo() throws Exception{
//    	DBTools tool = new DBTools();  
//    	LinkedList<Object> params = new LinkedList<Object>();
//        params.add(1);  
//        ResultSet rs = tool.executeQuery("SELECT 原商品编号,新商品编号,批次号 from 新商品编号 WHERE 1=?", params);  
//        while(rs.next()){
//        	StringBuilder result = new StringBuilder();
//        	String old = rs.getString(1);
//        	String newNo = rs.getString(2);
//        	String batchNo = rs.getString(3);
//        	result.append("update swift_const_item set keyd = '"+newNo+"' where code='cmbc_invoicing_params' and valued='"+batchNo+"' and keyd='"+old+"';");
//        	System.out.println(result.toString());
//        }
//    }
    
    public static void test20170725() throws Exception{
    	DBTools tool = new DBTools();  
    	LinkedList<Object> params = new LinkedList<Object>();
        params.add(1);  
        String p = "22977,22976,22975,22969,22968,22813,22812";
        params.add(p);
        ResultSet rs = tool.executeQuery("SELECT 商品编号,批次号 from 商品 WHERE 1=? and 批次号 not in(?)", params);  
        while(rs.next()){
        	
        	String id = rs.getString(1);
        	String batchNo = rs.getString(2);
        	if(StringUtils.isNotBlank(batchNo)){
        		StringBuilder result = new StringBuilder();
        		result.append("INSERT INTO `swift_const_item` VALUES (null, 'cmbc_invoicing_params', '"+batchNo+"', '"+id+"', '0', '1','');");
        		System.out.println(result.toString());
        	}
        }
    }
    
    public static void test20170809() throws Exception{
    	DBTools tool = new DBTools();  
    	LinkedList<Object> params = new LinkedList<Object>();
        params.add(1);  
        ResultSet rs = tool.executeQuery("SELECT 商品编号,批次号 from 新话费商品 WHERE 1=?", params);  
        while(rs.next()){
        	
        	String id = rs.getString(1);
        	String batchNo = rs.getString(2);
        	if(StringUtils.isNotBlank(batchNo)){
        		StringBuilder result = new StringBuilder();
        		result.append("INSERT INTO `swift_const_item` VALUES (null, 'cmbc_invoicing_params', '"+batchNo+"', '"+id+"', '0', '1','');");
        		System.out.println(result.toString());
        	}
        }
    }
    
    public static void testJdb0815() throws Exception{
    	String[] cityMap = {"菏泽"};
    	
    	DBTools tool = new DBTools();  
    	for(int i = 0;i < cityMap.length; i++){
    		StringBuilder result = new StringBuilder();
    		LinkedList<Object> params = new LinkedList<Object>();
    		String city = cityMap[i];
    		result.append("shopMap[\"" + city + "\"] = [");
            params.add(city);  
            ResultSet rs = tool.executeQuery("SELECT `门店名称`,`详细地址`,`联系人`,`联系电话` from `新门店0815` where 城市=?", params);  
            while(rs.next()){
            	String name = rs.getString(1);
            	String address = rs.getString(2);
            	String link = rs.getString(3);
            	String phone = rs.getString(4);
            	result.append("\"" + name + "," + address + "/" + link + phone +"\",");
            }
            String temp = result.toString().substring(0, result.toString().length() - 1);
            result = new StringBuilder(temp);
            result.append("];");
            System.out.println(result.toString());
    	}
    	
        tool.close();  
    }
    
    public static void testJdb0823() throws Exception{
    	String[] cityMap = {"临沂"};
    	
    	DBTools tool = new DBTools();  
    	for(int i = 0;i < cityMap.length; i++){
    		StringBuilder result = new StringBuilder();
    		LinkedList<Object> params = new LinkedList<Object>();
    		String city = cityMap[i];
    		result.append("shopMap[\"" + city + "\"] = [");
            params.add(city);  
            ResultSet rs = tool.executeQuery("SELECT `门店名称`,`详细地址`,`联系人`,`联系电话` from `8.21山东移动` where 城市=?", params);  
            while(rs.next()){
            	String name = rs.getString(1);
            	String address = rs.getString(2);
            	String link = rs.getString(3);
            	String phone = rs.getString(4);
            	result.append("\"" + name + "," + address + "/" + link + phone +"\",");
            }
            String temp = result.toString().substring(0, result.toString().length() - 1);
            result = new StringBuilder(temp);
            result.append("];");
            System.out.println(result.toString());
    	}
    	
        tool.close();  
    }
    
    public static void main(String[] args) throws Exception {
    	testJdb0823();
    }  
}  
